package com.ssyt.hiring.company.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.ssyt.hiring.common.PageModel;
import com.ssyt.hiring.common.util.DBUtil;
import com.ssyt.hiring.company.entity.PositionEntity;

public class PositionService {

	/**
	 * 查询当前登录企业的职位信息
	 * 
	 * @param id
	 * @return
	 */
	public List<PositionEntity> listById(int id) {
		String sql = "SELECT * FROM position WHERE company_id=? ORDER BY id DESC";
		List<PositionEntity> list = DBUtil.executeQuery(PositionEntity.class,
				sql, id);
		return list;
	}

	/**
	 * 添加职位
	 * 
	 * @param position
	 * @return
	 */
	public int add(PositionEntity position) {
		List<Object> list = new ArrayList<>();
		list.add(position.getCompanyId());
		list.add(position.getName());
		list.add(position.getDuty());
		list.add(position.getAbility());
		list.add(position.getSalary1());
		list.add(position.getSalary2());
		String sql = "INSERT INTO position VALUES(NULL ,?,?,?,?,?,?,NOW())";

		return DBUtil.executeUpdate(sql, list);
	}

	/**
	 * 根据ID查询职位信息
	 * 
	 * @param id
	 * @param companyId
	 * @return
	 */
	public PositionEntity preupdate(int id, int companyId) {
		String sql = "SELECT p.*,c.name as 'company$name' FROM position p,company c WHERE p.id=? and c.id=?";
		return DBUtil.getUniqueResult(PositionEntity.class, sql, id, companyId);
	}

	/**
	 * 根据ID修改职位信息
	 * 
	 * @param position
	 * @return
	 */
	public int update(PositionEntity position) {
		List<Object> params = new ArrayList<>();
		params.add(position.getName());
		params.add(position.getDuty());
		params.add(position.getAbility());
		params.add(position.getSalary1());
		params.add(position.getSalary2());
		params.add(position.getId());
		String sql = "UPDATE position SET name=?, duty=?, ability=?, salary1=?, salary2=? WHERE id=?";
		return DBUtil.executeUpdate(sql, params);
	}

	/**
	 * 根据ID删除职位信息
	 * 
	 * @param id
	 * @return
	 */
	public int delete(int id) {
		String sql = "DELETE FROM position WHERE id=?";
		return DBUtil.executeUpdate(sql, id);
	}

	public PageModel<PositionEntity> list(String searchText, int pageNo,
			int pageSize) {
		List<PositionEntity> positionList = new ArrayList<PositionEntity>();
		List<Object> paramList = new ArrayList<Object>();
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		StringBuilder sql = new StringBuilder(
				"SELECT c.name as 'company$name', c.address as 'company$address', c.slogan as 'company$slogan', p.id, p.name, p.salary1, p.salary2, p.release_time FROM company c, position p WHERE c.id=p.company_id ");
		if (StringUtils.isNotBlank(searchText)) {
			sql.append("AND (p.name LIKE ? OR c.name LIKE ?)");
			paramList.add("%" + searchText + "%");
			paramList.add("%" + searchText + "%");
		}
		sql.append(" ORDER BY p.id DESC LIMIT ?,?");
		paramList.add((pageNo - 1) * pageSize);
		paramList.add(pageSize);
		try {
			preparedStatement = connection.prepareStatement(sql.toString());
			DBUtil.setParameter(paramList, preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				PositionEntity positionEntity = new PositionEntity();
				positionEntity.setId(resultSet.getInt("id"));
				positionEntity.setName(resultSet.getString("name"));
				positionEntity.setSalary1(resultSet.getInt("salary1"));
				positionEntity.setSalary2(resultSet.getInt("salary2"));
				positionEntity.setReleaseTime(resultSet
						.getTimestamp("release_time"));
				positionEntity.setCompany$name(resultSet
						.getString("company$name"));
				positionEntity.setCompany$slogan(resultSet
						.getString("company$slogan"));
				positionEntity.setCompany$address(resultSet
						.getString("company$address"));
				positionList.add(positionEntity);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet, preparedStatement, connection);
		}
		return new PageModel<PositionEntity>(positionList,
				getTotalRecords(searchText), pageNo, pageSize);
	}

	private int getTotalRecords(String searchText) {
		int totalRecords = 0;
		StringBuilder sql = new StringBuilder(
				"SELECT COUNT(*) AS 'totalRecords' FROM position p, company c WHERE c.id=p.company_id ");
		if (StringUtils.isNotBlank(searchText)) {
			sql.append("AND (p.name LIKE ? OR c.name LIKE ?)");
		}
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			preparedStatement = connection.prepareStatement(sql.toString());
			if (StringUtils.isNotBlank(searchText)) {
				preparedStatement.setString(1, "%" + searchText + "%");
				preparedStatement.setString(2, "%" + searchText + "%");
			}
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			totalRecords = Integer
					.parseInt(resultSet.getString("totalRecords"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return totalRecords;
	}

	/**
	 * 根据ID查询详职位详细信息
	 * 
	 * @param id
	 * @return
	 */
	public PositionEntity detail(int id) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("c.name as 'company$name', ");
		sb.append("c.email as 'company$email', ");
		sb.append("c.phone as 'company$phone', ");
		sb.append("c.linkman as 'company$linkman', ");
		sb.append("c.address as 'company$address', ");
		sb.append("c.numbers as 'company$numbers', ");
		sb.append("c.slogan as 'company$slogan', ");
		sb.append("p.id, ");
		sb.append("p.name, ");
		sb.append("p.duty, ");
		sb.append("p.ability, ");
		sb.append("p.salary1, ");
		sb.append("p.salary2, ");
		sb.append("p.release_time ");
		sb.append("FROM company c, position p ");
		sb.append("WHERE c.id=p.company_id AND p.id=? ");
		return DBUtil.getUniqueResult(PositionEntity.class, sb.toString(), id);
	}

}
